﻿Imports MySql.Data.MySqlClient

Public Class CPembagianKelas
    Inherits CContext

    Private INSERT_COMMAND As String
    Private UPDATE_COMMAND As String
    Private DELETE_COMMAND As String
    Private SELECT_COMMAND As String

    Public Sub New()
        MyBase.New()
    End Sub

    ''' <summary>
    ''' Get all by semester dan kelas
    ''' </summary>
    ''' <param name="idSemester"></param>
    ''' <param name="idkelas"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getAll(ByVal idSemester As Integer, ByVal idkelas As Integer) As DataTable
        Me.SELECT_COMMAND = "SELECT * FROM tbl_PEMBAGIAN_KELAS WHERE ID_KLS=" & idkelas & " AND ID_SMSTR=" & idSemester
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    ''' <summary>
    ''' daftar siswa dalam kelas per semester tertentu
    ''' </summary>
    ''' <param name="idKelas"></param>
    ''' <param name="idSemester"></param>
    ''' <returns>id_smstr,id_kls,id_ssw,kelas, siswa</returns>
    ''' <remarks></remarks>
    Public Function getDistribusiKelas(idKelas As Integer, idSemester As Integer) As DataTable
        'Me.SELECT_COMMAND = "SELECT * FROM tbl_PEMBAGIAN_KELAS WHERE ID_KLS=" & idKelas & " AND ID_SMSTR=" & idSemester
        Me.SELECT_COMMAND = "SELECT pk.id_smstr,pk.id_kls,pk.id_ssw,k.nama as kelas, s.nama as siswa FROM  tbl_PEMBAGIAN_KELAS pk inner join tbl_siswa s on pk.id_ssw = s.id inner join  tbl_kelas k on pk.id_kls = k.id  WHERE ID_KLS=" & idKelas & " AND ID_SMSTR=" & idSemester
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    ''' <summary>
    ''' insert data pembagian kelas
    ''' data table (id_smstr,id_kls,id_ssw)
    ''' </summary>
    ''' <param name="dataPembagian"></param>
    ''' <remarks></remarks>
    Public Sub simpan(dataPembagian As DataTable)
        Dim _TMysql As MySql.Data.MySqlClient.MySqlTransaction
        MyBase.MySqlConnect.Open()
        _TMysql = MyBase.MySqlConnect.BeginTransaction

        Try



            For Each AROW As DataRow In dataPembagian.Rows
                Dim INSERT_PEMBAGIAN_COMMAND As MySql.Data.MySqlClient.MySqlCommand = MyBase.MySqlConnect.CreateCommand
                Dim SELECT_TINGKATAN_KELAS As MySqlCommand = MyBase.MySqlConnect.CreateCommand
                Dim UPDATE_TINGKATAN_SISWA As MySqlCommand = MyBase.MySqlConnect.CreateCommand

                'INSERT DATA PEMBAGIAN KELAS COMMAND
                INSERT_PEMBAGIAN_COMMAND.CommandText = "INSERT INTO TBL_PEMBAGIAN_KELAS (ID_SMSTR,ID_KLS,ID_SSW) VALUES (?ID_SMSTR,?ID_KLS,?ID_SSW)"
                INSERT_PEMBAGIAN_COMMAND.Transaction = _TMysql

                'SELECT TINGKATAN KELAS
                SELECT_TINGKATAN_KELAS.CommandText = "SELECT TNGKT FROM TBL_KELAS WHERE ID = ?ID"
                SELECT_TINGKATAN_KELAS.Transaction = _TMysql
                Dim tingkatReader As MySqlDataReader
                Dim tingkatan As Integer = 0

                'UPDATE TINGKATAN SISWA
                'UPDATE_TINGKATAN_SISWA.CommandText = "UPDATE TBL_SISWA SET TNGKT = ?TNGKT WHERE ID = ?ID"
                'UPDATE_TINGKATAN_SISWA.Transaction = _TMysql

                'insert pembagian kelas
                With INSERT_PEMBAGIAN_COMMAND
                    .Parameters.Add(New MySqlParameter("ID_SMSTR", AROW.Item("ID_SMSTR")))
                    .Parameters.Add(New MySqlParameter("ID_KLS", AROW.Item("ID_KLS")))
                    .Parameters.Add(New MySqlParameter("ID_SSW", AROW.Item("ID_SSW")))
                    .ExecuteNonQuery()
                End With
                'select tingkatan siswa
                With SELECT_TINGKATAN_KELAS
                    .Parameters.Add(New MySqlParameter("ID", AROW.Item("ID_KLS")))
                    tingkatReader = .ExecuteReader()
                    While tingkatReader.Read
                        tingkatan = tingkatReader.Item("TNGKT")
                    End While
                    tingkatReader.Close()

                End With

                'update tingkatan siswa
                With UPDATE_TINGKATAN_SISWA
                    .CommandText = "UPDATE TBL_SISWA SET TNGKT = '" & tingkatan & "' WHERE ID = " & AROW.Item("ID_SSW")
                    '.Parameters.Add(New MySqlParameter("ID", AROW.Item("ID_SSW")))
                    '.Parameters.Add(New MySqlParameter("TNGKT", tingkatan))
                    .Transaction = _TMysql
                    .ExecuteNonQuery()
                End With
            Next


            'Dim MYSQL_INSERT_CMD As MySql.Data.MySqlClient.MySqlCommand
            'For Each aRow As DataRow In DataKonsentrasi.Rows
            '    If Not IsDBNull(aRow.Item("id_guru")) Or aRow.Item("id_guru").ToString.Trim <> "" Then
            '        MYSQL_INSERT_CMD = MyBase.MySqlConnect.CreateCommand
            '        With MYSQL_INSERT_CMD
            '            .CommandText = "INSERT INTO TBL_KONSENTRASI(ID_GURU,ID_MP) VALUES (" & aRow.Item("ID_GURU") & "," & aRow.Item("ID") & ")"
            '            .Transaction = _TMysql
            '            .ExecuteNonQuery()
            '        End With
            '    End If
            'Next

            'commit command
            _TMysql.Commit()
        Catch ex As Exception
            _TMysql.Rollback()
            MsgBox(ex.Message)
        Finally
            MyBase.MySqlConnect.Close()
        End Try
    End Sub


End Class
